-- =========================
-- SEQUENCES
-- =========================
CREATE SEQUENCE STARTER_CONTINENT_ID_SEQ START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE STARTER_COUNTRY_ID_SEQ   START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE STARTER_CITY_ID_SEQ      START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE STARTER_PERSON_ID_SEQ    START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

-- =========================
-- TABLES
-- =========================
CREATE TABLE STARTER_CONTINENT (
  ID              NUMBER,
  CODE            VARCHAR2(20) NOT NULL,
  NAME            VARCHAR2(50) NOT NULL,
  WIKIPEDIA_LINK  VARCHAR2(200) DEFAULT '',
  AREA            NUMBER DEFAULT 0,
  POPULATION      NUMBER DEFAULT 0,
  COUNTRIES_NUMBER NUMBER DEFAULT 0,
  CONSTRAINT STARTER_CONTINENT_PK PRIMARY KEY (ID)
);

CREATE TABLE STARTER_COUNTRY (
  ID              NUMBER,
  NAME            VARCHAR2(50) NOT NULL,
  WIKIPEDIA_LINK  VARCHAR2(200) DEFAULT '',
  CONTINENT_ID    NUMBER,
  ISO_NUMERIC     VARCHAR2(50) NOT NULL,
  ISO_ALPHA2      VARCHAR2(50) NOT NULL,
  ISO_ALPHA3      VARCHAR2(50) NOT NULL,
  FLAG            VARCHAR2(50),
  CONSTRAINT STARTER_COUNTRY_PK PRIMARY KEY (ID),
  CONSTRAINT STARTER_COUNTRY_FK_CONTINENT FOREIGN KEY (CONTINENT_ID)
      REFERENCES STARTER_CONTINENT(ID)
);

CREATE TABLE STARTER_CITY (
  ID              NUMBER,
  NAME            VARCHAR2(50) NOT NULL,
  WIKIPEDIA_LINK  VARCHAR2(200) NOT NULL,
  COUNTRY_ID      NUMBER,
  CAPITAL         NUMBER(1) DEFAULT 0,
  CONSTRAINT STARTER_CITY_PK PRIMARY KEY (ID),
  CONSTRAINT STARTER_CITY_FK_COUNTRY FOREIGN KEY (COUNTRY_ID)
      REFERENCES STARTER_COUNTRY(ID)
);

CREATE TABLE STARTER_PERSON (
  ID              NUMBER,
  NAME            VARCHAR2(50) NOT NULL,
  WIKIPEDIA_LINK  VARCHAR2(200) NOT NULL,
  BIRTH_DATE      DATE,
  BIRTH_CITY_ID   NUMBER,
  DEATH_DATE      DATE,
  DEATH_CITY_ID   NUMBER,
  GENDER_ID       NUMBER,
  IMAGE           VARCHAR2(200),
  CONSTRAINT STARTER_PERSON_PK PRIMARY KEY (ID),
  CONSTRAINT STARTER_PERSON_FK_BIRTH_CITY FOREIGN KEY (BIRTH_CITY_ID) REFERENCES STARTER_CITY(ID),
  CONSTRAINT STARTER_PERSON_FK_DEATH_CITY FOREIGN KEY (DEATH_CITY_ID) REFERENCES STARTER_CITY(ID)
);

-- =========================
-- TRIGGERS
-- =========================
CREATE OR REPLACE TRIGGER STARTER_CONTINENT_BI
BEFORE INSERT ON STARTER_CONTINENT
FOR EACH ROW
BEGIN
  IF :NEW.ID IS NULL THEN
    :NEW.ID := STARTER_CONTINENT_ID_SEQ.NEXTVAL;
  END IF;
END;
/

CREATE OR REPLACE TRIGGER STARTER_COUNTRY_BI
BEFORE INSERT ON STARTER_COUNTRY
FOR EACH ROW
BEGIN
  IF :NEW.ID IS NULL THEN
    :NEW.ID := STARTER_COUNTRY_ID_SEQ.NEXTVAL;
  END IF;
END;
/

CREATE OR REPLACE TRIGGER STARTER_CITY_BI
BEFORE INSERT ON STARTER_CITY
FOR EACH ROW
BEGIN
  IF :NEW.ID IS NULL THEN
    :NEW.ID := STARTER_CITY_ID_SEQ.NEXTVAL;
  END IF;
END;
/

CREATE OR REPLACE TRIGGER STARTER_PERSON_BI
BEFORE INSERT ON STARTER_PERSON
FOR EACH ROW
BEGIN
  IF :NEW.ID IS NULL THEN
    :NEW.ID := STARTER_PERSON_ID_SEQ.NEXTVAL;
  END IF;
END;
/
